<?php
namespace bee;
require_once "lib/ext/PHPExcel.php";
require_once "lib/ext/PHPExcel/IOFactory.php";


class dbExcel
{ 
   /**
   * @var \PHPExcel
   */
  private $Excell;
  /**
   * @var \PHPExcel_Reader_Excel2007
   */
  private $Reader;
  /**
   * 
   * @var \PHPExcel_Writer_Excel2007
   */
  private $Writer;
  /**
   * @var \PHPExcel_Worksheet
   */
  private $Sheet;
  /**
   * @var \PHPExcel_Cell
   */
  private $Cell;
  /**
   * 
   * @var DAO
   */
  private $DAOTable;
  /**
   * 
   * @var array
   */
  private $importCols;
  /**
   * 
   * @var array
   */
  public $mapping;
  
  public $exception;
  public $ExcelDocName;
  public $excelType;
  public $rowOffset;
  public $colOffset;
  public $cols;
  public $rows;
  public $maxEmpties;
  
  public function __construct($ExcellDocName, array $options = array())
  { 
    $this->ExcelDocName=$ExcellDocName;  
    
    $this->rowOffset = isset($options["rowOffset"]) ? $options["rowOffset"] : 1;
    $this->colOffset = isset($options["colOffset"]) ? $options["colOffset"] : 0;
    
    $this->mapping = array();
    $this->importCols = array();
    
    $this->cols = 0;
    $this->rows = 0;
    $this->maxEmpties = 10;
  }
  
  /**
   * 
   * @param string $sheet
   * @param string $table
   * @return int
   */
  public function sheet2table($sheet = "", $table = "")
  { $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
    \PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
    $this->Reader = \PHPExcel_IOFactory::createReader(isset($options["excelType"]) ? $options["excelType"] : "Excel2007");
    $this->Reader->setReadDataOnly(true);
    if($sheet)
    { $this->Reader->setLoadSheetsOnly(array($sheet));
    }
    $this->Excell = $this->Reader->load($this->ExcelDocName);
    if($sheet)
    { $this->Sheet = $this->Excell->getSheetByName($sheet); 
    } else 
    { $this->Sheet = $this->Excell->getSheet(0);
    }
    
    if(!$table)
    { $table = $this->Sheet->getTitle();
    }
    //die($table);
    $this->DAOTable = new $table();
    
    for($i = 0; ($colName = $this->Sheet->getCellByColumnAndRow($this->colOffset + $i, $this->rowOffset)->getValue()); $i++)
    { if( $this->getMappedCol($colName))
      { $this->importCols[$i] = $this->getMappedCol($colName);
      }
      $this->cols++;
    }   
    //print_r($this->importCols);
    $empties = 0;
    $importedRows = 0;
    db::getInstance()->beginTransaction();
    for($i = $this->rowOffset + 1; $empties < $this->maxEmpties; $i++)
    { $allEmpty = true;
      foreach ($this->importCols as $colPos => $colName) 
      { if($cellVal = $this->Sheet->getCellByColumnAndRow($this->colOffset + $colPos, $i)->getValue())
        { $this->DAOTable->$colName = $cellVal;
          $allEmpty = false;
        }
      }
      
      if($allEmpty)
      { $empties++;
      } else
      { 
        if($this->DAOTable->INSERT())
        { $importedRows++;
        } else
        { db::getInstance()->rollBack();
          $this->exception = $this->DAOTable->__exception;
          //echo print_r($this->exception);
          return false;
          
        }
        
      }
    }
    db::getInstance()->commit();
    return $importedRows;
  }
  public function table2Sheet($table, $sheet = "")
  { $this->Writer = \PHPExcel_IOFactory::createWriter( "", isset($options["excelType"]) ? $options["excelType"] : "Excel2007");    
  }
  
  
  /**
   * 
   * @param string $excelName
   * @return string
   */
  private function getMappedCol($excelName)
  { 
    //print_r($this->DAOTable);
    if(!($key = array_search($excelName, $this->mapping)))
    { $key = $excelName;
    }
    
    if(property_exists($this->DAOTable, $key))
    { return $key;
    } else
    { return false;
    }
  }
    
}
